package cn.com.sinosoft.test;

import java.util.List;
import com.order.cc.sys.dao.FoJdbcDaoSupport;

public class DataPretreatmentJdbcDAO extends FoJdbcDaoSupport {

	public List<?> findPretCustomerList(String rolegroup_id,boolean inOrNotIn, int pageSize, int pageNo) {
		String startNo = (pageNo-1)*pageSize+1+"";
		String endNo = pageNo*pageSize+"";
//		String sql = "select * from (select ROW_NUMBER() OVER(order by t.CUST_ID) rn,t.* from OB_CUSTOMER_LIST t) where rn between ? and ?";
//		String sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM Customer c where c.OWNER is not null) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
//		String sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT c.CUSTOMERNAME,c.GENDER,c.BIRTHDAY,c.CUSTOMERTYPE_ID,c.CUSTOMERID,c.OWNER,c.MOBILE,c.DEGREE,c.HIGHT,c.WEIGHT,c.BMI,c.BLOOD_TYPE,c.MARRIAGE,c.STRINGFIELD21,c.CUSTOMER_GUID,c.ADDRESS,c.ZIPCODE,c.E_MAIL,c.INCOME,c.JOB1,c.JOB2,c.JOB_CODE,c.JOB3,c.POSITION,c.COMPANY,c.TEL_1,c.TEL_2,c.OTHERS,c.CREATEDBY,c.CREATEDDATE FROM Customer c where c.OWNER is not null) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
		//对联系电话进行处理的sql
		String sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT c.CUSTOMERNAME,c.GENDER,c.BIRTHDAY,c.CUSTOMERTYPE_ID,c.CUSTOMERID,c.OWNER,c.MOBILE,c.DEGREE,c.HIGHT,c.WEIGHT,c.BMI,c.BLOOD_TYPE,c.MARRIAGE,c.STRINGFIELD21,c.CUSTOMER_GUID,c.ADDRESS,c.ZIPCODE,c.E_MAIL,c.INCOME,c.JOB1,c.JOB2,c.JOB_CODE,c.JOB3,c.POSITION,c.COMPANY,c.TEL_1,c.TEL_2,c.OTHERS,c.CREATEDBY,c.CREATEDDATE,'老系统数据' as TRIPMODE FROM Customer c where c.OWNER in (SELECT st.staff_id FROM ROLEGROUP RGC INNER JOIN ROLEGROUP RGT ON RGC.MANAGERID = RGT.MANAGERID INNER JOIN STAFFROLEGROUP SRG ON RGT.ROLEGROUP_ID = SRG.ROLEGROUP_ID INNER JOIN STAFF ST ON SRG.STAFF_ID = ST.STAFF_ID WHERE RGC.STATUS = '1' AND RGC.TMIS_TYPE = '2' and rgt.status = '1' and rgt.TMIS_TYPE in ('1', '2') AND ST.WORKSTATUS = 'ONLINE' and RGT.rolegroup_id in (?))) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
		//正式sql
//		String sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT c.CUSTOMERNAME,c.GENDER,c.BIRTHDAY,c.CUSTOMERTYPE_ID,c.CUSTOMERID,c.OWNER,c.MOBILE,c.DEGREE,c.HIGHT,c.WEIGHT,c.BMI,c.BLOOD_TYPE,c.MARRIAGE,c.STRINGFIELD21,c.CUSTOMER_GUID,c.ADDRESS,c.ZIPCODE,c.E_MAIL,c.INCOME,c.JOB1,c.JOB2,c.JOB_CODE,c.JOB3,c.POSITION,c.COMPANY,c.TEL_1,c.TEL_2,c.OTHERS,c.CREATEDBY,c.CREATEDDATE FROM Customer c where c.OWNER is not null) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
		
		if(inOrNotIn){
			sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT c.CUSTOMERNAME,c.GENDER,c.BIRTHDAY,c.CUSTOMERTYPE_ID,c.CUSTOMERID,c.OWNER,c.MOBILE,c.DEGREE,c.HIGHT,c.WEIGHT,c.BMI,c.BLOOD_TYPE,c.MARRIAGE,c.STRINGFIELD21,c.CUSTOMER_GUID,c.ADDRESS,c.ZIPCODE,c.E_MAIL,c.INCOME,c.JOB1,c.JOB2,c.JOB_CODE,c.JOB3,c.POSITION,c.COMPANY,c.TEL_1,c.TEL_2,c.OTHERS,c.CREATEDBY,c.CREATEDDATE,'老系统数据' as TRIPMODE FROM Customer c where c.OWNER in (SELECT st.staff_id FROM ROLEGROUP RGC INNER JOIN ROLEGROUP RGT ON RGC.MANAGERID = RGT.MANAGERID INNER JOIN STAFFROLEGROUP SRG ON RGT.ROLEGROUP_ID = SRG.ROLEGROUP_ID INNER JOIN STAFF ST ON SRG.STAFF_ID = ST.STAFF_ID WHERE RGC.STATUS = '1' AND RGC.TMIS_TYPE = '2' and rgt.status = '1' and rgt.TMIS_TYPE in ('1', '2') AND ST.WORKSTATUS = 'ONLINE' and RGT.rolegroup_id in (?))) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
		}else{
			sql = "SELECT * FROM  ( SELECT A.*, ROWNUM RN FROM (SELECT c.CUSTOMERNAME,c.GENDER,c.BIRTHDAY,c.CUSTOMERTYPE_ID,c.CUSTOMERID,c.OWNER,c.MOBILE,c.DEGREE,c.HIGHT,c.WEIGHT,c.BMI,c.BLOOD_TYPE,c.MARRIAGE,c.STRINGFIELD21,c.CUSTOMER_GUID,c.ADDRESS,c.ZIPCODE,c.E_MAIL,c.INCOME,c.JOB1,c.JOB2,c.JOB_CODE,c.JOB3,c.POSITION,c.COMPANY,c.TEL_1,c.TEL_2,c.OTHERS,c.CREATEDBY,c.CREATEDDATE,'老系统数据' as TRIPMODE FROM Customer c where c.OWNER in (SELECT st.staff_id FROM ROLEGROUP RGC INNER JOIN ROLEGROUP RGT ON RGC.MANAGERID = RGT.MANAGERID INNER JOIN STAFFROLEGROUP SRG ON RGT.ROLEGROUP_ID = SRG.ROLEGROUP_ID INNER JOIN STAFF ST ON SRG.STAFF_ID = ST.STAFF_ID WHERE RGC.STATUS = '1' AND RGC.TMIS_TYPE = '2' and rgt.status = '1' and rgt.TMIS_TYPE in ('1', '2') AND ST.WORKSTATUS = 'ONLINE' and RGT.rolegroup_id not in (?))) A  WHERE ROWNUM <= ? ) WHERE RN >= ?";
		}
		return this.getJdbcTemplate().queryForList(sql, new Object[]{rolegroup_id, endNo, startNo});
	}
}
